﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Reviewer.Entities;
using System.Data;
using System.Collections.ObjectModel;
using System.Globalization;
using Reviewer.Utilities.Data;
using MySql.Data.MySqlClient;
using Reviewer.Data.Bases;
using System.Data.SqlClient;

namespace Reviewer.Data
{
    /// <summary>
    /// News Repository
    /// </summary>
    [CLSCompliant(false)]
    public class NewsRepository : RepositoryCrudAlternativeBase<News, Int32, Guid>
    {
        #region fields (private)
        private const string TABLE_NAME = "V2_Nyhed";
        private static readonly CrudColumnInfo columnId = new CrudColumnInfo(0, "Id", MySqlDbType.Int32, 255, true, false, false, true);
        private static readonly CrudColumnInfo columnNewsId = new CrudColumnInfo(1, "NewsId", MySqlDbType.VarChar, 36, false, true, false);
        private static readonly CrudColumnInfo columnTitle = new CrudColumnInfo(2, "Titel", MySqlDbType.VarChar, 255, false, false, true);
        private static readonly CrudColumnInfo columnHeader = new CrudColumnInfo(3, "Header", MySqlDbType.VarChar, 255, false, false, true);
        private static readonly CrudColumnInfo columnText = new CrudColumnInfo(4, "Tekst", MySqlDbType.Text, false, false, true);
        private static readonly CrudColumnInfo columnSource = new CrudColumnInfo(5, "Kilde", MySqlDbType.VarChar, 255, false, false, true);
        private static readonly CrudColumnInfo columnScreens = new CrudColumnInfo(6, "Screens", MySqlDbType.VarChar, 255, false, false, true);
        private static readonly CrudColumnInfo columnSubject = new CrudColumnInfo(7, "Emne", MySqlDbType.Int32, 255, false, false, true);
        private static readonly CrudColumnInfo columnAuthor = new CrudColumnInfo(8, "Forfatter", MySqlDbType.Int32, false, false, true);
        private static readonly CrudColumnInfo columnDate = new CrudColumnInfo(9, "Dato", MySqlDbType.DateTime, false, false, true);
        #endregion

        #region methods (public)
        /// <summary>
        /// Gets the latest new items.
        /// </summary>
        /// <param name="count">The count.</param>
        /// <returns></returns>
        public Collection<News> GetLatest(int count)
        {
            return base.SelectAll(count, NewsRepository.columnDate, SortOrder.Descending);
        }

        public Collection<News> SelectByGameRelation(Guid gameId)
        {

            using (MySqlCommand command = new MySqlCommand(String.Format(
                CultureInfo.InvariantCulture
                , "{0} LEFT OUTER JOIN V2_NewsRelations R ON (N.NewsId = R.NewsId) WHERE R.SpilId = @GameId"
                , this.SelectAllQuery("N"))))
            {
                command.Parameters.AddWithValue("@GameId", gameId.ToString());
                return base.ExecuteReaderAndBuildList(command);
            }
        }

        public Collection<News> Search(string value)
        {
            using (MySqlCommand command = new MySqlCommand(String.Format(
                CultureInfo.InvariantCulture
                , "{0} WHERE {1} LIKE @{1} OR {2} LIKE @{2} OR {3} LIKE @{3}"
                , this.SelectAllQuery()
                , NewsRepository.columnTitle.Name
                , NewsRepository.columnHeader.Name
                , NewsRepository.columnText.Name)))
            {
                NewsRepository.columnTitle.AddParameter(command, "%" + value + "%");
                NewsRepository.columnHeader.AddParameter(command, "%" + value + "%");
                NewsRepository.columnText.AddParameter(command, "%" + value + "%");

                return base.ExecuteReaderAndBuildList(command);
            }
        }

        public Collection<News> SelectByFormat(int formatId)
        {
            using (MySqlCommand command = new MySqlCommand(String.Format(
                CultureInfo.InvariantCulture
                , "{0} LEFT OUTER JOIN {1} B ON (N.NewsId = B.NewsId) WHERE B.Format = {2}"
                , this.SelectAllQuery("N")
                , "V2_NewsFormat"
                , formatId)))
            {
                return base.ExecuteReaderAndBuildList(command);
            }
        }

        public ICollection<News> Search(int? newsType, int? format, int maxResults)
        {
            using (MySqlCommand command = new MySqlCommand())
            {
                StringBuilder query = new StringBuilder();
                query.Append(this.SelectAllQuery("N"));
                if (format.HasValue && format.Value > 0)
                {
                    query.Append(" LEFT OUTER JOIN V2_NewsFormat NF ON (NF.NewsId = N.NewsId)");
                }

                query.Append(" WHERE 1=1");
                if (newsType.HasValue && newsType.Value > 0)
                {
                    query.AppendFormat(CultureInfo.InvariantCulture, " AND N.{0} = @{0}", NewsRepository.columnSubject.Name);
                    NewsRepository.columnSubject.AddParameter(command, newsType.Value);
                }

                if (format.HasValue && format.Value > 0)
                {

                    query.Append(" AND NF.Format = @FormatId");
                    command.Parameters.AddWithValue("@FormatId", format.Value);
                }
                query.AppendFormat(CultureInfo.InvariantCulture, " ORDER BY N.{0} DESC", NewsRepository.columnId.Name);
                query.Append(" LIMIT " + maxResults);
                command.CommandText = query.ToString();

                return base.ExecuteReaderAndBuildList(command);
            }
        }


        #endregion

        #region methods (protected)
        internal override CrudColumnInfo[] Columns
        {
            get
            {
                return new CrudColumnInfo[]{
                    NewsRepository.columnId
                    ,NewsRepository.columnNewsId
                    ,NewsRepository.columnTitle
                    ,NewsRepository.columnHeader
                    ,NewsRepository.columnText
                    ,NewsRepository.columnSource
                    ,NewsRepository.columnScreens 
                    ,NewsRepository.columnSubject
                    ,NewsRepository.columnAuthor
                    ,NewsRepository.columnDate};
            }
        }

        protected override string TableName
        {
            get { return NewsRepository.TABLE_NAME; }
        }

        internal override object ColumnToValue(CrudColumnInfo info, News item)
        {
            if (info.Equals(NewsRepository.columnId)) return item.Id;
            else if (info.Equals(NewsRepository.columnNewsId)) return item.NewsId;
            else if (info.Equals(NewsRepository.columnTitle)) return item.Title;
            else if (info.Equals(NewsRepository.columnHeader)) return item.Header;
            else if (info.Equals(NewsRepository.columnText)) return item.Text;
            else if (info.Equals(NewsRepository.columnSource)) return item.Source;
            else if (info.Equals(NewsRepository.columnScreens)) return item.ShowRelatedScreenshots;
            else if (info.Equals(NewsRepository.columnSubject)) return item.NewsType;
            else if (info.Equals(NewsRepository.columnAuthor)) return item.Author;
            else if (info.Equals(NewsRepository.columnDate)) return item.NewsDate;
            return null;
        }

        protected override News BuildItemFromReader(IDataReader reader)
        {
            News item = new News();
            if (reader != null)
            {
                item.Id = DataReaderUtility.GetValue(reader, reader.GetOrdinal(NewsRepository.columnId.Name), 0);
                item.NewsId = DataReaderUtility.GetValue(reader, reader.GetOrdinal(NewsRepository.columnNewsId.Name), Guid.Empty);
                item.Title = DataReaderUtility.GetValue(reader, reader.GetOrdinal(NewsRepository.columnTitle.Name), String.Empty);
                item.Header = DataReaderUtility.GetValue(reader, reader.GetOrdinal(NewsRepository.columnHeader.Name), String.Empty);
                item.Text = DataReaderUtility.GetValue(reader, reader.GetOrdinal(NewsRepository.columnText.Name), String.Empty);
                item.Source = DataReaderUtility.GetValue(reader, reader.GetOrdinal(NewsRepository.columnSource.Name), String.Empty);
                item.ShowRelatedScreenshots = DataReaderUtility.GetValue(reader, reader.GetOrdinal(NewsRepository.columnScreens.Name), false);
                item.NewsType = DataReaderUtility.GetValue(reader, reader.GetOrdinal(NewsRepository.columnSubject.Name), 0);
                item.Author = DataReaderUtility.GetValue(reader, reader.GetOrdinal(NewsRepository.columnAuthor.Name), 0);
                item.NewsDate = DataReaderUtility.GetValue(reader, reader.GetOrdinal(NewsRepository.columnDate.Name), DateTime.MinValue);
            }
            return item;
        }
        #endregion
    }
}
